﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Odbc;
using System.Data;

/// <summary>
/// Summary description for DbUtilities
/// </summary>
public class DbUtilities
{
    private OdbcConnection myConn = new OdbcConnection("Driver={MySQL ODBC 5.1 Driver};Server=localhost;Database=feedback;User=root;Password=mysql;");
    private String sqlQuery;

    public DbUtilities()
    {

    }
 
	public DbUtilities(String query)
	{
        sqlQuery = query;
	}

    public void writeInfo()
    {
        myConn.Open();
        OdbcCommand cmd = new OdbcCommand(sqlQuery, myConn);
        cmd.ExecuteNonQuery();
        myConn.Close();
    }

    public DataSet retrieveInfo()
    {
        myConn.Open();
        OdbcCommand cmd = new OdbcCommand(sqlQuery, myConn);
        OdbcDataAdapter odbcAdapter = new OdbcDataAdapter(cmd);
        DataSet dataSet = new DataSet();
        odbcAdapter.Fill(dataSet);
        myConn.Close();
        return dataSet;
    }

    public String retrieveCount()
    {
        myConn.Open();
        OdbcCommand cmd = new OdbcCommand(sqlQuery, myConn);
        String count = cmd.ExecuteScalar().ToString();
        myConn.Close();
        return count;
    }

    public bool checkDatabaseForPassword(string UserName, string Password)
    {
        bool boolReturnValue = false;
        
        String strSQL = "Select * From admin";
        myConn.Open();
        OdbcCommand cmd = new OdbcCommand(strSQL, myConn);
        OdbcDataReader reader;
        reader = cmd.ExecuteReader();
        while (reader.Read())
        {

            if ((UserName == "admin") & (Password == reader["Password"].ToString()))
            {
                boolReturnValue = true;

            }
            reader.Close();
            return boolReturnValue;
        }
        myConn.Close();
        return false;
        
    }

    public string checkDatabaseForEmail_ID(string Email_ID)
    {
        string password = null;
        String strSQL = "Select * From admin";
        myConn.Open();
        OdbcCommand cmd = new OdbcCommand(strSQL, myConn);
        OdbcDataReader reader;
        reader = cmd.ExecuteReader();

        while (reader.Read())
        {

            if ((Email_ID == reader["Admin_Email"].ToString()))
            {
                password = reader["Password"].ToString();

            }
            reader.Close();
            myConn.Close();
            return password;
        }
        myConn.Close();
        return password;
        

    }

    public bool checkDatabaseForOldPassword(string oldPassword)
    {

        bool isConfirm = false;
        String strSQL = "Select * From admin";
        myConn.Open();
        OdbcCommand cmd = new OdbcCommand(strSQL, myConn);
        OdbcDataReader reader;
        reader = cmd.ExecuteReader();
        while (reader.Read())
        {

            if ((oldPassword == reader["Password"].ToString()))
            {
                isConfirm = true;

            }
            reader.Close();
            myConn.Close();
            return isConfirm;
        }
        myConn.Close();
        return isConfirm;

    }

    public void changePassword(string oldPassword, string newPassword)
    {
        String strSQL = "update admin set Password = '" + newPassword + "'where Password= '" + oldPassword + "'";
        myConn.Open();
        OdbcCommand cmd = new OdbcCommand(strSQL, myConn);
        OdbcDataReader reader;
        reader = cmd.ExecuteReader();
        reader.Close();
        myConn.Close();
    }

}